package com.timeDeal.service;



import com.timeDeal.bean.JingbiaoBean;
import com.timeDeal.bean.PageList;
import com.timeDeal.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static java.sql.Types.NULL;

/**
 * Created by 叼爸爸 on 2016/9/6.
 */
public class JIngbiaoServicce {
    public  boolean tijiaoJingbiao(JingbiaoBean jb , String leixing){
        Connection cn = DButil.getConnection();
        String mysql="insert into  jingbiao (jingbiaoid ,xuqiufabuid,jingbiaorenid,jingbiaotiaojian,jingbiaoyoushi )"
              +" values(?,?,?,?,?)";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setInt(1,DButil.getZhuJianMax("jingbiaoid","jingbiao"));
            ps.setInt(2,jb.getXuqiufabuid());
            ps.setInt(3,jb.getJingbiaorenid());
            ps.setString(4,jb.getJingbiaotiaojian());
            ps.setString(5,jb.getJingbiaoyoushi());
                  ps.execute();
            return true ;
            }
          catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false ;
    }
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJingbiaoById(int cu , int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  jingbiao   where  jingbiaorenid= ? and shijianchushouid is NULL "   ;
        String mysqljingbiao="select  *    from  jingbiao   where jingbiaorenid= ? and shijianchushouid is NULL limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        System.out.println(mysql);
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setInt(1,id);
            System.out.println("DDDDDDDDDDDDDDDDDDD"+id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljingbiao);
            System.out.println(mysqljingbiao);
            ps.setInt(1,id);
            rs=ps.executeQuery() ;
            while (rs.next()){

                JingbiaoBean jingbiaoBean = new JingbiaoBean() ;
                jingbiaoBean.setJingbiaoid(rs.getInt("jingbiaoid"));
                jingbiaoBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                jingbiaoBean.setJingbiaotiaojian(rs.getString("jingbiaotiaojian"));
                jingbiaoBean.setJingbiaoyoushi(rs.getString("jingbiaoyoushi"));
                System.out.println("DDDDDDDDDDDDDDDDDDD"+rs.getInt("xuqiufabuid"));
                list.add(jingbiaoBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJingbiaoByIdAndShijian(int cu , int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  jingbiao   where  jingbiaorenid= ? and xuqiufabuid is NULL "   ;
        String mysqljingbiao="select  *    from  jingbiao   where jingbiaorenid= ?  and xuqiufabuid is NULL  limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setInt(1,id);

            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljingbiao);
            ps.setInt(1,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                JingbiaoBean jingbiaoBean = new JingbiaoBean() ;
                jingbiaoBean.setJingbiaoid(rs.getInt("jingbiaoid"));
                jingbiaoBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                jingbiaoBean.setJingbiaotiaojian(rs.getString("jingbiaotiaojian"));
                jingbiaoBean.setJingbiaoyoushi(rs.getString("jingbiaoyoushi"));
                list.add(jingbiaoBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
}
